//
//  SQL_DataBase.m
//  Food
//
//  Created by dllo on 15/7/22.
//  Copyright (c) 2015年 dllo. All rights reserved.
//

#import "SQL_DataBase.h"
@implementation SQL_DataBase

+ (SQL_DataBase *) shareInstance
{
    static SQL_DataBase *dbManager;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        dbManager = [[SQL_DataBase alloc] init];
    });
    return dbManager;
}

- (void) openDB
{
    //1. 准备数据库路径
    NSString *path = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSLog(@"%@", path);
    NSString *dbPath = [path stringByAppendingPathComponent:@"Kitchen.db"];
    //2. 打开
    int result = sqlite3_open([dbPath UTF8String], &dbPointer);
    //3. 判断
    if (result == SQLITE_OK) {
        NSLog(@"数据库创建成功");
    }
    else
    {
        NSLog(@"数据库 创建失败  原因:%d", result);
    }
}

- (void)closeDB
{
    sqlite3_close(dbPointer);
}

- (void) creatTable1
{
    NSString *creatTable = @"create table if not exists MyCollect (classid text primary key, classname text, imagePath text)";
    int result = sqlite3_exec(dbPointer, [creatTable UTF8String], nil, nil, nil);
    [self panduan:result type:@"创建表"];
}

- (void) insertCollect:(HomePage_detailed_model *) model
{
   // NSInteger classid = [model.classId intValue];
    NSLog(@"%@", model.classId);
    NSString *insertSQL = [NSString stringWithFormat:@"insert into MyCollect (classid, classname, imagePath) values ('%@', '%@', '%@')", model.classId, model.name, model.imagePath];
    int result = sqlite3_exec(dbPointer, [insertSQL UTF8String], nil, nil, nil);
    [self panduan:result type:@"插入数据"];
}

- (void)deleteCollect:(NSString *) classid
{
   // NSInteger classId = [classid integerValue];
    NSString *deleteSQL = [NSString stringWithFormat:@"delete from MyCollect where classid = '%@'", classid];
    int result = sqlite3_exec(dbPointer, [deleteSQL UTF8String], nil, nil, nil);
    [self panduan:result type:@"删除"];
}

- (HomePage_detailed_model *) selectCollectID:(NSString *)classID
{
    NSString *selectSQL = [NSString stringWithFormat:@"select * from MyCollect where classid = '%@'",classID];
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare(dbPointer, [selectSQL UTF8String], -1, &stmt, nil);
    
    HomePage_detailed_model *model = [[HomePage_detailed_model alloc] init];
    
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //int classid = sqlite3_column_int(stmt, 0);
            const unsigned char *classid = sqlite3_column_text(stmt, 0);
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *imagePath = sqlite3_column_text(stmt, 2);
            model.classId = [NSString stringWithUTF8String:(const char *)classid];
            model.name = [NSString stringWithUTF8String:(const char *)name];
            model.imagePath = [NSString stringWithUTF8String:(const char *)imagePath];
        }
        NSLog(@"查询成功");
    }
    else
    {
        NSLog(@"查询失败   %d", result);
    }
    return model;
}


- (NSMutableArray *) selectCollect
{
    NSString *selectSQL = @"select * from MyCollect";
    //替身
    sqlite3_stmt *stmt = nil;
    //准备sql
    int result = sqlite3_prepare(dbPointer, [selectSQL UTF8String], -1, &stmt, nil);
    
    NSMutableArray *arry = [NSMutableArray array];
    
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //int classid = sqlite3_column_int(stmt, 0);
             const unsigned char *classid = sqlite3_column_text(stmt, 0);
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *imagePath = sqlite3_column_text(stmt, 2);
            HomePage_detailed_model *model = [[HomePage_detailed_model alloc] init];
            model.classId = [NSString stringWithUTF8String:(const char *)classid];
            model.name = [NSString stringWithUTF8String:(const char *)name];
            model.imagePath = [NSString stringWithUTF8String:(const char *)imagePath];
            [arry addObject:model];
        }
        NSLog(@"查询成功");
    }
    else
    {
        NSLog(@"查询失败   %d", result);
    }
    return arry;
}


- (void) creatTable2
{
    NSString *creatTable = @"create table if not exists MyActivity (freshId text primary key, name text, content text, titleImageFile text)";
    int result = sqlite3_exec(dbPointer, [creatTable UTF8String], nil, nil, nil);
    [self panduan:result type:@"创建表"];
}

- (void) insertActivity:(AdventureModel *) model
{
    NSString *insertSQL = [NSString stringWithFormat:@"insert into MyActivity (freshId, name, content, titleImageFile) values ('%@', '%@', '%@', '%@')", model.freshId, model.name, model.content, model.titleImageFile];
    int result = sqlite3_exec(dbPointer, [insertSQL UTF8String], nil, nil, nil);
    [self panduan:result type:@"插入数据"];
}

- (void)deleteActivity:(NSString *) freshId
{
    NSString *deleteSQL = [NSString stringWithFormat:@"delete from MyActivity where freshId = '%@'", freshId];
    int result = sqlite3_exec(dbPointer, [deleteSQL UTF8String], nil, nil, nil);
    [self panduan:result type:@"删除"];
}

- (AdventureModel *) selectActivityID:(NSString *)freshId
{
    NSString *selectSQL = [NSString stringWithFormat:@"select * from MyActivity where freshId = '%@'",freshId];
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare(dbPointer, [selectSQL UTF8String], -1, &stmt, nil);
    
    AdventureModel *model = [[AdventureModel alloc] init];
    
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *freshId = sqlite3_column_text(stmt, 0);
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *content = sqlite3_column_text(stmt, 2);
            const unsigned char *titleImageFile = sqlite3_column_text(stmt, 3);
            model.freshId = [NSString stringWithUTF8String:(const char *)freshId];
            model.name = [NSString stringWithUTF8String:(const char *)name];
            model.content = [NSString stringWithUTF8String:(const char *)content];
            model.titleImageFile = [NSString stringWithUTF8String:(const char *)titleImageFile];
        }
        NSLog(@"查询成功");
    }
    else
    {
        NSLog(@"查询失败   %d", result);
    }
    return model;
}


- (NSMutableArray *) selectActivity
{
    NSString *selectSQL = @"select * from MyActivity";
    //替身
    sqlite3_stmt *stmt = nil;
    //准备sql
    int result = sqlite3_prepare(dbPointer, [selectSQL UTF8String], -1, &stmt, nil);
    
    NSMutableArray *arry = [NSMutableArray array];
    
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *freshId = sqlite3_column_text(stmt, 0);
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *content = sqlite3_column_text(stmt, 2);
            const unsigned char *titleImageFile = sqlite3_column_text(stmt, 3);
            AdventureModel *model = [[AdventureModel alloc] init];
            model.freshId = [NSString stringWithUTF8String:(const char *)freshId];
            model.name = [NSString stringWithUTF8String:(const char *)name];
            model.content = [NSString stringWithUTF8String:(const char *)content];
            model.titleImageFile = [NSString stringWithUTF8String:(const char *)titleImageFile];
            [arry addObject:model];
        }
        NSLog(@"查询成功");
    }
    else
    {
        NSLog(@"查询失败   %d", result);
    }
    return arry;
}

- (void) panduan:(int)result type:(NSString *)str
{
    if (result == SQLITE_OK) {
        NSLog(@"%@ 操作成功", str);
    }
    else
    {
        NSLog(@"%@ 操作失败   原因:%d", str, result);
    }
}


@end
